package com.dhy.dao;

import com.dhy.dbCore.util.DbUtil;
import com.dhy.dbCore.JdbcTemplateFacade;
import com.dhy.dto.BachelorDto;

import java.util.List;

public class BachelorDao {

    private JdbcTemplateFacade jt = DbUtil.getJT();

    // 查询学生的学位信息
    public List<BachelorDto> queryBachelorBySno(String sno) {
        String sql = "select t.id, t.name as stuName, t.sno, t.credits, b.name as bachelorName, bp.pub as pub " +
                "from bachelorPub bp " +
                "join (select * from stu s where sno = " + sno + ") t " +
                "join bachelor b " +
                "where bp.stuId = t.id and b.id = bp.id;";
        return jt.list(sql, BachelorDto.class);
    }

    // 查询学生学位证是否发放
    public Integer queryPubBySno(String sno) {
        String sql = "select bP.pub from bachelorPub bP " +
                "join (select * from stu s where sno = " + sno + ") t " +
                "where bP.stuId = t.id;";
        return jt.queryOne(sql, Integer.class);
    }

    // 删除学位信息
    public Boolean deleteBachelor(String bid) {
        String sql = "delete from bachelor where id = '" + bid + "';";
        return jt.delete(sql);
    }
}
